package org.langqiao.excel;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class InsertSqlBuild {

    /*public static void main(String[] args) throws IOException {
        InputStream inputStream = new FileInputStream("c://query-impala-586159(1).xlsx");
        ReadExcel readExcel = new ReadExcel(inputStream);

        StringBuffer insertSql = new StringBuffer();
        List<ArrayList<String>> data = readExcel.readAll();
        for (int i = 0; i < data.size(); i++) {

            if (i % 1000 == 0) {
                if(i != 0)
                    insertSql.append(";\n");
                insertSql.append("INSERT INTO tb_rule_act_configuration (rule_act_id,rule_content,upper_limit,lower_limit,extend_content) VALUES")
                        .append("(7,").append(data.get(i).get(0)).append(",'0','0','0')");
            } else {
                insertSql.append(",(7,").append(data.get(i).get(0)).append(",'0','0','0')");
            }
        }

        ForFile.createFile("homePagePopup_userId.sql", insertSql.toString());
    }*/

    /*public static void main(String[] args) throws IOException {
        InputStream inputStream = new FileInputStream("c://10.20.xlsx");
        ReadExcel readExcel = new ReadExcel(inputStream);

        StringBuffer insertSql = new StringBuffer();
        List<ArrayList<String>> data = readExcel.readAll();
        for (int i = 0; i < data.size(); i++) {

            if (i % 1000 == 0) {
                if(i != 0)
                    insertSql.append(";\n");
                insertSql.append("INSERT INTO tb_init_amount (user_id) VALUES")
                        .append("(").append(data.get(i).get(0)).append(")");
            } else {
                insertSql.append(",(").append(data.get(i).get(0)).append(")");
            }
        }

        ForFile.createFile("ti_e_userId.sql", insertSql.toString());
    }*/

    public static void main(String[] args) throws IOException, ParseException {
    	SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
    	try {
    		String str = "06/07/2018";
    		java.util.Date date = sdf.parse(str);
    		System.out.println(date);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
    	
    	SimpleDateFormat sdf111 = new SimpleDateFormat("yyyy-MM-dd");
    	
        InputStream inputStream = new FileInputStream("e:\\blacklist_20181229.xlsx");
        ReadExcel readExcel = new ReadExcel(inputStream);

//        StringBuffer insertSql = new StringBuffer("INSERT INTO `tb_u_black_list_info` (`ktp_no`,`name`,`mobile`,`creation_date`,`join_fraud_reason`,`reason_type`,`into_status`,`operator_id`) VALUES ");
        StringBuffer insertSql = new StringBuffer("INSERT INTO `tb_u_black_list_info` (`name`,`ktp_no`,`mobile`,`creation_date`,`into_status`,`reason_type`,`operator_id`) VALUES ");
        List<ArrayList<String>> data = readExcel.readAll();
        try {
        	for (int i = 1; i < data.size(); i++) {
        		System.out.println(i);
        		insertSql.append("(")
        		.append("'").append(data.get(i).get(0)).append("',")
        		.append("'").append(data.get(i).get(1)).append("',")
        		.append("'").append(data.get(i).get(2)).append("',")
        		.append("'").append(sdf111.format(new Date())).append("',")
        		.append("'").append(data.get(i).get(3)).append("',")
        		.append("'").append(data.get(i).get(4)).append("',")
        		.append("-1")
        		.append("),\n");
        		
        	}
		} catch (Exception e) {
			e.printStackTrace();
		}

        ForFile.createFile("tb_u_black_list_info.sql", insertSql.toString());
    }
}
